import json
from typing import List, Optional, Any

import frappe
from frappe.utils import today as _today


def _coalesce_company() -> Optional[str]:
    """Resolve company strictly from User Permission(Company) of current user.

    - If user has one or more User Permission records for Company, return the first non-empty value.
    - If none found or on error, return None (no fallback to global defaults).
    """
    try:
        user = getattr(frappe.session, "user", "Guest")
        perms = frappe.get_all(
            "User Permission",
            filters={"user": user, "allow": "Company"},
            pluck="for_value",
        )
        companies = [p for p in perms if p]
        if companies:
            # 若存在多个公司权限，取第一个；统计接口本身目前只支持单公司过滤
            return companies[0]
        return None
    except Exception:
        return None


def _table_exists(table: str) -> bool:
    try:
        exists = bool(frappe.db.table_exists(table))
        return exists
    except Exception:
        return False


def _first_existing_table(candidates: List[str]) -> Optional[str]:
    for tbl in candidates:
        if _table_exists(tbl):
            return tbl
    return None


def _sum_payment(company: Optional[str]) -> float:
    """Sum recharge amount from oms_payment-like table filtered by company if column exists."""
    table_candidates = ["taboms_payment", "tabOms Payment", "tabOMS Payment"]
    table = _first_existing_table(table_candidates)
    if not table:
        return 0.0

    try:
        columns = set(frappe.db.get_table_columns(table))
    except Exception:
        columns = set()

    amount_fields = ["amount", "recharge_amount", "paid_amount", "payment_amount", "total_amount"]
    amount_col = next((c for c in amount_fields if c in columns), None) or "amount"
    company_fields = ["company", "company_name", "oms_company"]
    company_col = next((c for c in company_fields if c in columns), None)

    try:
        if company and company_col:
            sql = f"SELECT COALESCE(SUM(`{amount_col}`), 0) FROM `{table}` WHERE `{company_col}`=%s"
            total = float(frappe.db.sql(sql, (company,))[0][0] or 0)
        else:
            sql = f"SELECT COALESCE(SUM(`{amount_col}`), 0) FROM `{table}`"
            total = float(frappe.db.sql(sql)[0][0] or 0)
        return total
    except Exception:
        return 0.0


def _count_items(company: Optional[str]) -> int:
    if not _table_exists("tabItem"):
        return 0

    # 0) Preferred: by Item Default -> Account.company == target company
    try:
        if company and _table_exists("tabItem Default") and _table_exists("tabAccount"):
            sql = (
                "SELECT COUNT(DISTINCT i.name) "
                "FROM `tabItem` i JOIN `tabItem Default` d ON d.parent=i.name "
                "LEFT JOIN `tabAccount` ai ON ai.name=d.income_account "
                "LEFT JOIN `tabAccount` ae ON ae.name=d.expense_account "
                "WHERE i.disabled=0 AND (ai.company=%s OR ae.company=%s)"
            )
            cnt = int(frappe.db.sql(sql, (company, company))[0][0] or 0)
            if cnt:
                return cnt
    except Exception:
        pass

    try:
        cols = set(frappe.db.get_table_columns("tabItem"))
    except Exception:
        cols = set()

    if company and "company" in cols:
        cnt = int(frappe.db.count("Item", filters={"company": company, "disabled": 0}) or 0)
        return cnt

    # Item Default child table
    try:
        if company and _table_exists("tabItem Default"):
            sql = (
                "SELECT COUNT(DISTINCT i.name) FROM `tabItem` i JOIN `tabItem Default` d ON d.parent=i.name "
                "WHERE i.disabled=0 AND d.company=%s"
            )
            cnt = int(frappe.db.sql(sql, (company,))[0][0] or 0)
            if cnt:
                return cnt
    except Exception:
        pass

    # Bin + Warehouse
    args = []
    where = ""
    if company:
        where = "WHERE w.company=%s"
        args = [company]
    sql = (
        "SELECT COUNT(DISTINCT b.item_code) FROM `tabBin` b JOIN `tabWarehouse` w ON w.name=b.warehouse "
        f"{where}"
    )
    try:
        cnt = int(frappe.db.sql(sql, args)[0][0] or 0)
        if cnt:
            return cnt
    except Exception:
        pass

    # Fallback: total active items
    try:
        cnt = int(frappe.db.count("Item", filters={"disabled": 0}) or 0)
        return cnt
    except Exception:
        return 0


def _wdt_snapshot_inventory_stats(company: Optional[str]) -> dict:
    """Return both SKU-with-stock count and total quantity from latest WDT snapshot.

    统一一次 SQL 统计两类数据，避免重复查询：
    - sku_count:  wdt_quantity > 0 的去重 Item 数量
    - qty_sum:    所有行 wdt_quantity 之和
    """
    if not company or not _table_exists("tabWDT Shopify Inventory Snapshot Inst"):
        return {"sku_count": 0, "qty_sum": 0.0}
    try:
        latest_row = frappe.db.sql(
            """
            SELECT MAX(snapshot_date)
            FROM `tabWDT Shopify Inventory Snapshot Inst`
            WHERE company=%s
            """,
            (company,),
        )
        if not latest_row or not latest_row[0][0]:
            return {"sku_count": 0, "qty_sum": 0.0}
        latest_date = latest_row[0][0]

        sku_count, qty_sum = frappe.db.sql(
            """
            SELECT
                COUNT(DISTINCT CASE WHEN COALESCE(wdt_quantity, 0) > 0 THEN item END) AS sku_count,
                COALESCE(SUM(wdt_quantity), 0)                                       AS qty_sum
            FROM `tabWDT Shopify Inventory Snapshot Inst`
            WHERE company=%s
              AND snapshot_date=%s
            """,
            (company, latest_date),
        )[0]
        return {
            "sku_count": int(sku_count or 0),
            "qty_sum": float(qty_sum or 0.0),
        }
    except Exception:
        return {"sku_count": 0, "qty_sum": 0.0}


def _have_stock_item_count(company: Optional[str]) -> int:
    """Count SKUs with stock on hand from latest WDT Shopify Inventory Snapshot Inst."""
    stats = _wdt_snapshot_inventory_stats(company)
    return int(stats.get("sku_count", 0))


def _sum_reserved_logistics_today(company: Optional[str]) -> float:
    """Sum reserved_logistics_today from Acct Balance for the given company."""
    if not company:
        return 0.0
    try:
        if not _table_exists("tabAcct Balance"):
            return 0.0
        rows = frappe.get_all(
            "Acct Balance",
            filters={"company": company},
            fields=["reserved_logistics_today"],
            limit_page_length=0,
        )
        total = 0.0
        for r in rows:
            try:
                total += float(r.get("reserved_logistics_today") or 0)
            except Exception:
                continue
        return total
    except Exception:
        return 0.0


def _total_inventory_qty(company: Optional[str]) -> float:
    """Sum of WDT quantity for latest snapshot under current company."""
    stats = _wdt_snapshot_inventory_stats(company)
    return float(stats.get("qty_sum", 0.0))


def _order_counts(company: Optional[str]) -> dict:
    """目前仅用于首页“订单”模块的待审核统计。

    - pending_review: 所有处于草稿状态的销售订单（docstatus=0）
    """
    if not _table_exists("tabSales Order"):
        return {"pending_review": 0}

    args = []
    where_company = ""
    if company:
        where_company = " AND company=%s"
        args.append(company)

    sql_review = (
        "SELECT COUNT(*) FROM `tabSales Order` "
        "WHERE docstatus=0" + where_company
    )
    pending_review = int(frappe.db.sql(sql_review, tuple(args))[0][0] or 0)
    return {"pending_review": pending_review}


def _shipments_today(company: Optional[str]) -> int:
    if not _table_exists("tabDelivery Note"):
        return 0
    args = []
    where_company = ""
    if company:
        where_company = " AND company=%s"
        args.append(company)
    sql = "SELECT COUNT(*) FROM `tabDelivery Note` WHERE docstatus=1 AND posting_date=CURDATE()" + where_company
    try:
        cnt = int(frappe.db.sql(sql, tuple(args))[0][0] or 0)
        return cnt
    except Exception:
        return 0


def _orders_to_deliver(company: Optional[str]) -> int:
    """Count Sales Orders with status = 'To Deliver' (submitted)."""
    try:
        filters = [
            ["Sales Order", "docstatus", "=", 1],
            ["Sales Order", "status", "=", "To Deliver"],
        ]
        if company:
            filters.append(["Sales Order", "company", "=", company])
        return _rv_get_count("Sales Order", filters=filters)
    except Exception:
        return 0


def _orders_count_by_status(company: Optional[str], status: str, docstatus: int = 1) -> int:
    """Generic counter for Sales Order by business status and docstatus."""
    try:
        filters = [
            ["Sales Order", "docstatus", "=", int(docstatus)],
            ["Sales Order", "status", "=", status],
        ]
        if company:
            filters.append(["Sales Order", "company", "=", company])
        return _rv_get_count("Sales Order", filters=filters)
    except Exception:
        return 0


def _orders_new_today_draft(company: Optional[str]) -> int:
    """Count Sales Orders in Draft with transaction_date = today.

    Use reportview.get_count to align with List View behaviour and permission rules.
    """
    try:
        filters = [
            ["Sales Order", "docstatus", "=", 0],
            ["Sales Order", "transaction_date", "=", _today()],
        ]
        if company:
            filters.append(["Sales Order", "company", "=", company])
        return _rv_get_count("Sales Order", filters=filters)
    except Exception:
        return 0


def _logistics_status_counters(company: Optional[str]) -> dict:
    """Aggregate Logistics Order Inst into three buckets for home dashboard.

    规则（按状态码首字母判断，兼容 'D:Delivered' 这类写法）：
    - 异常：code in ('E', 'N') 或 issue_status = 'Processing'
    - 送达：code = 'D'
    - 运输中：除上述两类以外的所有记录（包含 F/T/P/R/C 等，以及空值）
    """
    try:
        args: list[Any] = []
        where_company = ""
        if company:
            where_company = " AND company=%s"
            args.append(company)

        row = frappe.db.sql(
            f"""
            SELECT
                SUM(
                    CASE
                        WHEN (
                              TRIM(SUBSTRING_INDEX(IFNULL(logistics_status_code, ''), ':', 1)) IN ('E','N')
                              OR issue_status = 'Processing')
                        THEN 1 ELSE 0
                    END
                ) AS exception_cnt,
                SUM(
                    CASE
                        WHEN TRIM(SUBSTRING_INDEX(IFNULL(logistics_status_code, ''), ':', 1)) = 'D'
                        THEN 1 ELSE 0
                    END
                ) AS delivered_cnt,
                SUM(
                    CASE
                        WHEN
                            (TRIM(SUBSTRING_INDEX(IFNULL(logistics_status_code, ''), ':', 1)) <> 'D')
                            AND NOT (TRIM(SUBSTRING_INDEX(IFNULL(logistics_status_code, ''), ':', 1)) IN ('E','N')
                                     OR issue_status = 'Processing')
                        THEN 1 ELSE 0
                    END
                ) AS in_transit_cnt
            FROM `tabLogistics Order Inst`
            WHERE 1=1 {where_company}
            """,
            tuple(args),
        )
        if not row:
            return {"exception": 0, "in_transit": 0, "delivered": 0}
        exc, delivered, in_transit = row[0]
        counters = {
            "exception": int(exc or 0),
            "in_transit": int(in_transit or 0),
            "delivered": int(delivered or 0),
        }
        frappe.logger().error(
            f"[HomeStats] _logistics_status_counters company={company}, result={counters}"
        )
        return counters
    except Exception:
        frappe.logger().error(
            f"[HomeStats] _logistics_status_counters exception company={company}, traceback={frappe.get_traceback()}"
        )
        return {"exception": 0, "in_transit": 0, "delivered": 0}


def _pending_not_shipped_buckets(company: Optional[str]) -> dict:
    """Group pending (to deliver) sales orders by age: 1d, 2d, 3d+ based on transaction_date."""
    if not _table_exists("tabSales Order"):
        return {"d1": 0, "d2": 0, "d3p": 0}
    args = []
    where_company = ""
    if company:
        where_company = " AND company=%s"
        args.append(company)
    base = (
        "FROM `tabSales Order` WHERE docstatus=1 AND status IN ('To Deliver')"
        + where_company
    )
    sql1 = "SELECT COUNT(*) " + base + " AND DATEDIFF(CURDATE(), transaction_date) <= 1"
    sql2 = "SELECT COUNT(*) " + base + " AND DATEDIFF(CURDATE(), transaction_date) = 2"
    sql3 = "SELECT COUNT(*) " + base + " AND DATEDIFF(CURDATE(), transaction_date) >= 3"
    try:
        d1 = int(frappe.db.sql(sql1, tuple(args))[0][0] or 0)
        d2 = int(frappe.db.sql(sql2, tuple(args))[0][0] or 0)
        d3p = int(frappe.db.sql(sql3, tuple(args))[0][0] or 0)
        return {"d1": d1, "d2": d2, "d3p": d3p}
    except Exception:
        return {"d1": 0, "d2": 0, "d3p": 0}


@frappe.whitelist()
def get_home_stats() -> dict:
    """Return aggregated stats for Flowa home dashboard, filtered by user's company."""
    company = _coalesce_company()
    try:
        orders = _order_counts(company)
        # “待发货” 与 “已发货” 直接基于 Sales Order 业务状态统计
        pending_review = orders.get("pending_review", 0)
        to_deliver = _orders_to_deliver(company)
        shipped = _orders_count_by_status(company, "Completed", 1)
        buckets = _pending_not_shipped_buckets(company)
        logistics_counters = _logistics_status_counters(company)
        stats = {
            "company": company or "",
            "balance": _sum_payment(company),
            "reserved_logistics_today": _sum_reserved_logistics_today(company),
            # 与 Desk 列表页保持一致：通过 reportview.get_count 统计启用的 Item 数量
            "item_count": _rv_get_count("Item", filters=[["Item", "disabled", "=", 0]]),
            "have_stock_item_count": _have_stock_item_count(company),
            "total_inventory_qty": _total_inventory_qty(company),
            # 首页订单：只展示 3 个核心业务状态
            "orders_pending_review": pending_review,
            "orders_to_deliver": to_deliver,
            "orders_shipped": shipped,
            # 下面这些字段暂时保留，方便后续如需扩展（前端当前不再展示）
            "orders_new_today": _orders_new_today_draft(company),
            "not_shipped_1d": buckets.get("d1", 0),
            "not_shipped_2d": buckets.get("d2", 0),
            "not_shipped_3p": buckets.get("d3p", 0),
            # Logistics snapshot stats (from Logistics Order Inst)
            # 使用 _logistics_status_counters 聚合三类
            "logistics_exception": logistics_counters.get("exception", 0),
            "logistics_in_transit": logistics_counters.get("in_transit", 0),
            "logistics_delivered": logistics_counters.get("delivered", 0),
        }
        if not any([
            stats.get("balance"),
            stats.get("item_count"),
            stats.get("have_stock_item_count"),
            stats.get("total_inventory_qty"),
            stats.get("orders_new_today"),
            stats.get("orders_pending_review"),
            stats.get("orders_pending_handover"),
        ]):
            pass
    except Exception:
        stats = {
            "company": company or "",
            "balance": 0.0,
            "item_count": 0,
            "have_stock_item_count": 0,
            "total_inventory_qty": 0.0,
            "orders_new_today": 0,
            "orders_pending_review": 0,
            "orders_pending_handover": 0,
        }
    return stats



# --- Generic wrappers that reuse Frappe's reportview.get_count (same as Desk shortcuts) ---

def _rv_get_count(doctype: str, filters=None, distinct: bool = False, limit: int | None = None) -> int:
    """Call frappe.desk.reportview.get_count using the same code path as workspace shortcuts.

    This builds a temporary form_dict and invokes reportview.get_count so permission checks,
    virtual doctypes, child-table distinct handling, etc. behave exactly the same.
    """
    from frappe.desk.reportview import get_count as rv_get_count

    prev_form = getattr(frappe.local, "form_dict", frappe._dict())
    # ensure we don't mutate the original dict instance
    saved = frappe._dict(prev_form.copy())
    try:
        # reportview.get_count expects JSON-ish strings in form_dict and parses them internally
        fd = frappe._dict()
        fd.doctype = doctype
        fd.filters = filters if isinstance(filters, (list, dict)) else []
        # pass as JSON strings where expected; the helper will json.loads internally
        fd.filters = frappe.as_json(fd.filters) if not isinstance(fd.filters, str) else fd.filters
        fd.fields = "[]"
        fd.distinct = int(bool(distinct))  # sbool() inside reportview accepts truthy strings/ints
        if limit is not None:
            fd.limit = int(limit)

        frappe.local.form_dict = fd
        cnt = int(rv_get_count() or 0)
        return cnt
    finally:
        frappe.local.form_dict = saved


@frappe.whitelist()
def get_item_available_count() -> int:
    """Return count of enabled Items using the exact same logic as Workspace shortcut.

    Equivalent to: frappe.db.count("Item", filters={"disabled": 0}) but routed through
    reportview.get_count for consistency with Desk/Workspace behaviour.
    """
    filters = [["Item", "disabled", "=", 0]]
    return _rv_get_count("Item", filters=filters)


@frappe.whitelist()
def get_doctype_count(doctype: str, stats_filter: str | None = None) -> int:
    """Generic count API mirroring Workspace Shortcut behaviour.

    Args:
        doctype: Target DocType to count
        stats_filter: JSON string or Python dict/list accepted by reportview filters
    """
    try:
        filters = frappe.parse_json(stats_filter) if stats_filter else []
    except Exception:
        filters = []
    return _rv_get_count(doctype, filters=filters)


@frappe.whitelist()
def get_sales_order_counts(company: str | None = None) -> dict:
    """Sales Order status summary aligned to our status chain.

    Mapping:
    - new_today: 草稿 (docstatus=0) 且今日创建
    - submitted_total: 提交状态总数 (docstatus=1)
    - to_deliver: 待出货 = status in ("To Deliver") and docstatus=1
    - to_bill: 待开票 = status in ("To Bill") and docstatus=1
    """
    if not company:
        company = _coalesce_company()

    args: list = []
    where_company = ""
    if company:
        where_company = " AND company=%s"
        args.append(company)

    # 今日新增（草稿）
    sql_new = (
        "SELECT COUNT(*) FROM `tabSales Order` "
        "WHERE docstatus=0 AND DATE(creation)=CURDATE()" + where_company
    )

    # 提交总数（待审核）
    sql_submitted = "SELECT COUNT(*) FROM `tabSales Order` WHERE docstatus=1" + where_company

    # 待出货（To Deliver）
    sql_to_deliver = (
        "SELECT COUNT(*) FROM `tabSales Order` "
        "WHERE docstatus=1 AND status IN ('To Deliver')" + where_company
    )

    # 待开票（To Bill）
    sql_to_bill = (
        "SELECT COUNT(*) FROM `tabSales Order` "
        "WHERE docstatus=1 AND status IN ('To Bill')" + where_company
    )

    try:
        new_today = int(frappe.db.sql(sql_new, tuple(args))[0][0] or 0)
        submitted_total = int(frappe.db.sql(sql_submitted, tuple(args))[0][0] or 0)
        to_deliver = int(frappe.db.sql(sql_to_deliver, tuple(args))[0][0] or 0)
        to_bill = int(frappe.db.sql(sql_to_bill, tuple(args))[0][0] or 0)
    except Exception:
        new_today = submitted_total = to_deliver = to_bill = 0

    return {
        "company": company or "",
        "new_today": new_today,
        "submitted_total": submitted_total,
        "to_deliver": to_deliver,
        "to_bill": to_bill,
    }